In today's technological environment, it is common for enterprises such as business organizations, schools, charitable organizations and government organizations to deploy private computer networks—intranets—to securely share such organization's information or network operating system within that organization. The term “intranet” is used in contrast to “internet”, which is a network between and among individuals and organizations, the most common of which is the global, public Internet. That is, an intranet is a network within an enterprise which sometimes refers only to an internal (private) website, but may be a more extensive part of the enterprise's information technology (IT) infrastructure. For example, in many enterprises, an employee may work from home and gain access to the enterprise via a portal requiring additional security hurdles such as synchronous keys for entry. An enterprise intranet may host multiple private websites and constitute an important component and focal point of internal communication with, and collaboration among, individual computer users associated/affiliated with the enterprise (e.g., students within a university, co-workers within a company, members of a governmental department or the like).
As individual computer users associated/affiliated with the enterprise perform various computer-based activities while logged into the intranet, these users may utilize enterprise resource planning (ERP) client/server systems as provided by the enterprise. One such system known as the Dynamics® AX 2009 ERP system available from Microsoft Corporation of Redmond, Wash. provides a database query server and associated application object server among other features. These are routinely accessed by a client, for example, to query the database for data contained in tables. Data that may populate a given field of a business document or form and may be recorded in several different database tables. The database may, thus, comprise a plurality of related tables of data, for example, related to everyday activities of the enterprise and related by the data recorded in the table.
A member of the enterprise may perform a query of the database known as a join query in which, for example, in the structured query language (SQL), a join query relates to a query in which two (or more) tables of data of a database are temporarily joined in order to select data of the tables. To perform a join query, information in the query may comprise at least one table, a column or row and a condition to make the join temporarily occur. A join query may involve selection of data for update. An example follows:
UPDATE aSET a.[updated_column] =updatevalueFROM tablebJOIN tablecON ...WHERE ...In this UPDATE, there is a JOIN of tableb and tablec and there is also an updatevalue, for example, based on a WHERE condition.
In the known Dynamics AX 2009 ERP system, a join query may be processed at the database each time the join is executed. A join query typically involves paging. Paging obtains its name from accessing pages of data of a database. Paging may begin, for example, with a structured query language (SQL) SELECT. For example, given a SQL and a selected row in the result of the query, one may generate two separate queries such that a “forward” query results in all subsequent rows and a “backward” query results in all previous rows of the result query. In a known process, upon receipt of an outer join query and based on the values of a particular row from a query result, a query result may fetch all forward and backward rows of the query by generating separate queries from the original query. One is for forward paging or a forward cursor and one is for backward paging or a reverse cursor, both of which directions being used to create a filter.
An exemplary process for processing an inner join may be taken from a Microsoft Business Framework (MBF) Persistent block reading design comprising generation of a where tree, This exemplary process described below is specific only for a value-based paging for an inner join. A problem may be defined as follows for providing for forward and backward paging: given an SQL SELECT query and a selected row in the result of that query, generate two separate queries such that a forward query and a backward query yield all subsequent and previous rows of the query. In particular, given a plurality of n Order By Segments (OBS1-n) present in an Order By and the Values (V1-n) for each segment in the current row and an operator (op1-n), a where expression may be generated of the form:(((OBS1==V1)&&(OBS2==V2) . . . && (OBSnopnVn))∥((OBS1=V1)&& (OBS2==V2) . . . && (OBSn-1Vn-1))∥((OBS1==V1)&& (OBS2op2V2))∥(OBS1op1V1))If the original query Order By does not cover any unique key fields, then, remaining unique key fields are explicitly added. The operator opn is determined by the type of cursor or paging direction and the sort order for the nth segment according to the following table:
Sort OrderForward PagingReverse PagingAscending><Descending<>This algorithm, however, does not process outer joins successfully. The algorithm uses a comparison operator, for example, one of >, <, or = to construct a where tree. The where tree will fetch forward and backward (reverse) pages. However, since an outer join may involve the presence of NULLable records, (records with NULL value entries), processing outer join cannot presently be supported by the above algorithm. The comparison operators will provide inaccurate results or no results when a query will retrieve data results with NULL values.
A comparison operator such as > or < compares values for greater than or less than. As used herein, a comparison operator may be an inequality operator which may be defined as an operator which does not involve equality or =. Since there may be no value entered in a row of a table with a NULL, then, an inequality operator is ineffective for outer join.
Fundamentally, then, there is a need in the art for performing outer joins in addition to inner joins, for example, by value-based positioning and so be able to perform both inner and outer joins for, for example, a query statement of joined tables of an enterprise database.
Thus, while it may be known to provide for inner joins for forward and backward paging where tables have completed values, however, what are needed are systems, methods and computer program products for adapting these existing processes for performing outer join queries in an enterprise resource planning system.